/*
Navicat SQL Server Data Transfer

Source Server         : LOCALHOST
Source Server Version : 110000
Source Host           : .:1433
Source Database       : AAACHAM2
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2014-04-25 11:09:34
*/


-- ----------------------------
-- Table structure for tb3523
-- ----------------------------
DROP TABLE [dbo].[tb3523]
GO
CREATE TABLE [dbo].[tb3523] (
[SOrderID] varchar(20) NOT NULL ,
[VoucherTypeID] varchar(20) NULL ,
[VoucherNo] varchar(20) NULL ,
[OrderDate] datetime NULL ,
[ContractNo] varchar(50) NULL ,
[ContractDate] datetime NULL ,
[ClassifyID] varchar(20) NULL ,
[OrderType] tinyint NOT NULL DEFAULT ((0)) ,
[ObjectID] varchar(20) NULL ,
[DeliveryAddress] nvarchar(250) NULL ,
[Notes] nvarchar(250) NULL ,
[Disabled] tinyint NOT NULL DEFAULT ((0)) ,
[OrderStatus] tinyint NOT NULL DEFAULT ((0)) ,
[QuotationID] varchar(250) NULL ,
[CreateDate] datetime NULL ,
[CreateUserID] varchar(20) NULL ,
[LastModifyUserID] varchar(20) NULL ,
[LastModifyDate] datetime NULL ,
[Ana01ID] varchar(20) NULL ,
[Ana02ID] varchar(20) NULL ,
[Ana03ID] varchar(20) NULL ,
[Ana04ID] varchar(20) NULL ,
[Ana05ID] varchar(20) NULL ,
[CurrencyID] varchar(20) NULL ,
[ExchangeRate] money NULL ,
[InventoryTypeID] varchar(20) NULL ,
[TranMonth] int NULL ,
[TranYear] int NULL ,
[DivisionID] varchar(20) NULL ,
[EmployeeID] varchar(20) NULL ,
[Transport] nvarchar(500) NULL ,
[PaymentID] varchar(20) NULL ,
[ObjectName] nvarchar(250) NULL ,
[VatNo] varchar(20) NULL ,
[Address] nvarchar(250) NULL ,
[IsPeriod] tinyint NULL ,
[IsPlan] tinyint NULL ,
[DepartmentID] varchar(20) NULL ,
[SalesManID] varchar(20) NULL ,
[ShipDate] datetime NULL ,
[InheritSOrderID] varchar(500) NULL ,
[DueDate] datetime NULL ,
[PaymentTermID] varchar(20) NULL ,
[FileType] int NULL ,
[Contact] nvarchar(250) NULL ,
[VATObjectID] varchar(20) NULL ,
[VATObjectName] nvarchar(250) NULL ,
[IsInherit] tinyint NULL ,
[ContactID] varchar(20) NULL 
)


GO

-- ----------------------------
-- Indexes structure for table tb3523
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table tb3523
-- ----------------------------
ALTER TABLE [dbo].[tb3523] ADD PRIMARY KEY NONCLUSTERED ([SOrderID])
GO

-- ----------------------------
-- Checks structure for table tb3523
-- ----------------------------
ALTER TABLE [dbo].[tb3523] ADD CHECK (([SOrderID] IS NOT NULL AND [SOrderID]<>''))
GO

-- ----------------------------
-- Triggers structure for table tb3523
-- ----------------------------
--DROP TRIGGER [dbo].[OX2001]
GO
CREATE TRIGGER [dbo].[OX2001]
ON [dbo].[tb3523]
AFTER DELETE
AS


Update tb3531 Set OrderStatus = 0
From tb3531  T00 inner join Deleted Del on T00.QuotationID = Del.QuotationID 
Where T00.OrderStatus = 1

Delete tb3014 Where PeriodID = (Select SOrderID From Deleted )



GO
--DROP TRIGGER [dbo].[OY2001]
GO
CREATE TRIGGER [dbo].[OY2001]
ON [dbo].[tb3523]
AFTER INSERT
AS

Update tb3531 Set OrderStatus = 1
From tb3531  T00 inner join Inserted Ins on T00.QuotationID = Ins.QuotationID 
Where T00.OrderStatus = 0

If not exists (Select top 1 1 From tb3014 inner join Inserted Ins on Ins.SOrderID = tb3014.PeriodID Where Ins.IsPeriod = 1)
Insert tb3014 (PeriodID, Description, Notes,  
	FromMonth, FromYear, ToMonth, ToYear, 
	CreateDate, CreateUserID, LastModifyDate, LastModifyUserID )
 Select      SOrderID,Notes, Notes, TranMonth, TranYear, TranMonth, TranYear, 
	CreateDate, CreateUserID, LastModifyDate, LastModifyUserID
From Inserted Ins
 Where Ins.IsPeriod = 1



GO
--DROP TRIGGER [dbo].[OZ2001]
GO
CREATE TRIGGER [dbo].[OZ2001]
ON [dbo].[tb3523]
AFTER UPDATE
AS
DECLARE 	@QuotationID_Old varchar(20),
		@QuotationID_New varchar(20),	
		@cur cursor

Set @cur =  cursor scroll keyset for 
		Select isnull(Del.QuotationID, ''), isnull(Ins.QuotationID,'')  
		From Deleted Del inner join Inserted Ins on Del.SOrderID = Ins.SOrderID 

OPEN @cur
FETCH NEXT FROM  @cur INTO  @QuotationID_Old, @QuotationID_New

WHILE @@FETCH_STATUS = 0
BEGIN
	If  @QuotationID_Old <> @QuotationID_New
		BEGIN
		If @QuotationID_Old <> ''
			Update tb3531 Set OrderStatus = 0
			Where OrderStatus = 1 and QuotationID = @QuotationID_Old
		
		If  @QuotationID_New <> ''
			Update tb3531 Set OrderStatus = 1
			Where OrderStatus = 0 and QuotationID = @QuotationID_New
		END 

	FETCH NEXT FROM  @cur INTO  @QuotationID_Old, @QuotationID_New
END





If not exists (Select top 1 1 From tb3014 inner join Inserted Ins on Ins.SOrderID = tb3014.PeriodID Where Ins.IsPeriod = 1)
Insert tb3014 (PeriodID, Description, Notes,  
	FromMonth, FromYear, ToMonth, ToYear, 
	CreateDate, CreateUserID, LastModifyDate, LastModifyUserID )
 Select      SOrderID,Notes, Notes, TranMonth, TranYear, TranMonth, TranYear, 
	CreateDate, CreateUserID, LastModifyDate, LastModifyUserID
From Inserted Ins
 Where Ins.IsPeriod = 1
Else
Update tb3014 set 	Description =Ins.Notes,
			Notes  = Ins.Notes,
			FromMonth = Ins.TranMonth, 
			FromYear = Ins.TranYear, 
			ToMonth = Ins.TranMonth, 
			ToYear = Ins.TranYear, 
			LastModifyDate = Ins.LastModifyDate , 
			LastModifyUserID = Ins.LastModifyUserID
From tb3014 inner join Inserted Ins on Ins.SOrderID = tb3014.PeriodID Where Ins.IsPeriod = 1






GO
